--
-- Database: `mymsdn`
--
CREATE SCHEMA `mymsdn`;
USE `mymsdn`;

-- --------------------------------------------------------

--
-- Table structure for table `history`
--

CREATE TABLE IF NOT EXISTS `history` (
  `HistoryId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Initiator` varchar(100) NOT NULL,
  `Start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `End` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
  `Request` int(10) unsigned,
  `Latency` int(10) unsigned,
  `Payload` int(10) unsigned,
  `Inserted` int(10) unsigned,
  `Updated` int(10) unsigned,
  `Deleted` int(10) unsigned,
  `Error` int(10) unsigned,
  `Status` varchar(20),
  PRIMARY KEY (`HistoryId`)
) CHARACTER SET = utf8;
-- --------------------------------------------------------

--
-- Table structure for table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `CategoryId` int(10) unsigned NOT NULL,
  `Name` varchar(100) NOT NULL,
  `Enabled` bit(1) NOT NULL DEFAULT b'1',
  PRIMARY KEY (`CategoryId`),
  UNIQUE KEY (`CategoryId`)
) CHARACTER SET = utf8;
-- --------------------------------------------------------

--
-- Table structure for table `family`
--

CREATE TABLE IF NOT EXISTS `family` (
  `FamilyId` int(10) unsigned NOT NULL,
  `Name` varchar(100) NOT NULL,
  `Enabled` bit(1) NOT NULL DEFAULT b'1',
  `CategoryId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`FamilyId`),
  UNIQUE KEY (`FamilyId`),
  KEY `FK_Category` (`CategoryId`),
  CONSTRAINT `FK_Category` FOREIGN KEY (`CategoryId`) REFERENCES `category` (`CategoryId`)
) CHARACTER SET = utf8;
-- --------------------------------------------------------

--
-- Table structure for table `file`
--

CREATE TABLE IF NOT EXISTS `file` (
  `FileId` int(10) unsigned NOT NULL,
  `FileName` varchar(200) NOT NULL,
  `Description` varchar(200) NOT NULL,
  `Sha1` varchar(40) NOT NULL,
  `PostedDate` timestamp NOT NULL,
  `Locale` varchar(2) NOT NULL,
  `DisplaySize` varchar(50) NOT NULL,
  
  `Size` bigint(20) unsigned,
  `eMule` varchar(32),
  
  `Enabled` bit(1) NOT NULL DEFAULT b'1',
  `FamilyId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`FileId`),
  UNIQUE KEY (`FileId`),
  KEY `FK_Family` (`FamilyId`),
  CONSTRAINT `FK_Family` FOREIGN KEY (`FamilyId`) REFERENCES `family` (`FamilyId`)
) CHARACTER SET = utf8;
-- --------------------------------------------------------

--
-- Table structure for table `checksum`
--

CREATE TABLE IF NOT EXISTS `checksum` (
  `ChecksumId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FileName` varchar(200) NOT NULL,
  `Sha1` varchar(40) NOT NULL,
  `Size` bigint(20) unsigned NOT NULL,
  `eMule` varchar(32) NOT NULL,

  `SubmittedDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
  `Initiator` varchar(100) NOT NULL,
  PRIMARY KEY (`ChecksumId`)
) CHARACTER SET = utf8;
-- --------------------------------------------------------

--
-- Table structure for table `locale`
--

CREATE TABLE IF NOT EXISTS `locale` (
  `Code` varchar(2) NOT NULL,
  `DisplayName` varchar(100) NOT NULL,
  `LocalizedName` varchar(100),

  `Enabled` bit(1) NOT NULL DEFAULT b'1',
  PRIMARY KEY (`Code`)
) CHARACTER SET = utf8;
-- --------------------------------------------------------

--
-- Content for table `locale`
--
INSERT INTO `locale` VALUES ('en','English',null,b'1');
INSERT INTO `locale` VALUES ('ar','Arabic',null,b'1');
INSERT INTO `locale` VALUES ('eu','Basque',null,b'1');
INSERT INTO `locale` VALUES ('bg','Bulgarian',null,b'1');
INSERT INTO `locale` VALUES ('ca','Catalan',null,b'1');
INSERT INTO `locale` VALUES ('hk','Chinese - Hong Kong SAR',null,b'1');
INSERT INTO `locale` VALUES ('cn','Chinese - Simplified',null,b'1');
INSERT INTO `locale` VALUES ('tw','Chinese - Taiwan',null,b'1');
INSERT INTO `locale` VALUES ('hr','Croatian',null,b'1');
INSERT INTO `locale` VALUES ('cs','Czech',null,b'1');
INSERT INTO `locale` VALUES ('da','Danish',null,b'1');
INSERT INTO `locale` VALUES ('nl','Dutch',null,b'1');
INSERT INTO `locale` VALUES ('et','Estonian',null,b'1');
INSERT INTO `locale` VALUES ('fi','Finnish',null,b'1');
INSERT INTO `locale` VALUES ('fr','French',null,b'1');
INSERT INTO `locale` VALUES ('gl','Galician',null,b'1');
INSERT INTO `locale` VALUES ('de','German',null,b'1');
INSERT INTO `locale` VALUES ('el','Greek',null,b'1');
INSERT INTO `locale` VALUES ('he','Hebrew',null,b'1');
INSERT INTO `locale` VALUES ('hu','Hungarian',null,b'1');
INSERT INTO `locale` VALUES ('it','Italian',null,b'1');
INSERT INTO `locale` VALUES ('ja','Japanese',null,b'1');
INSERT INTO `locale` VALUES ('ko','Korean',null,b'1');
INSERT INTO `locale` VALUES ('ky','Kyrgyz',null,b'1');
INSERT INTO `locale` VALUES ('lv','Latvian',null,b'1');
INSERT INTO `locale` VALUES ('lt','Lithuanian',null,b'1');
INSERT INTO `locale` VALUES ('no','Norwegian',null,b'1');
INSERT INTO `locale` VALUES ('pl','Polish',null,b'1');
INSERT INTO `locale` VALUES ('pt','Portuguese-Brazil',null,b'1');
INSERT INTO `locale` VALUES ('pp','Portuguese-Portugal',null,b'1');
INSERT INTO `locale` VALUES ('ro','Romanian',null,b'1');
INSERT INTO `locale` VALUES ('ru','Russian',null,b'1');
INSERT INTO `locale` VALUES ('sr','Serbian',null,b'1');
INSERT INTO `locale` VALUES ('sk','Slovak',null,b'1');
INSERT INTO `locale` VALUES ('sl','Slovenian',null,b'1');
INSERT INTO `locale` VALUES ('es','Spanish',null,b'1');
INSERT INTO `locale` VALUES ('sv','Swedish',null,b'1');
INSERT INTO `locale` VALUES ('th','Thai',null,b'1');
INSERT INTO `locale` VALUES ('tr','Turkish',null,b'1');
INSERT INTO `locale` VALUES ('uk','Ukrainian',null,b'1');
INSERT INTO `locale` VALUES ('ct','Chinese - Traditional',null,b'1');

INSERT INTO `locale` VALUES ('__','Multi-Language',null,b'1');
INSERT INTO `locale` VALUES ('mu','Multi-Language',null,b'0');
INSERT INTO `locale` VALUES ('na','Not Available',null,b'0');


-- --------------------------------------------------------------------------------
-- Routine sp_syncchecksum
-- Note: sync checksum for table `file`
-- --------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS `sp_syncchecksum`;
DELIMITER $$CREATE PROCEDURE `sp_syncchecksum` ()BEGIN    UPDATE `file` as a left join `checksum` as b on 	a.`Sha1` = b.`Sha1` and a.`FileName` = b.`FileName`    SET
    a.`Size` = b.`Size`,    a.`eMule` = b.`eMule`
    WHERE a.`Size` is null and a.`eMule` is null;END$$DELIMITER ;